﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using SARS.DAO.Facades;
using SARS.Entity;
using SARS.Entity.Factory;

namespace SARS.DAO
{
    public class UserDAO : IUserDAO
    {
        private readonly SqlConnection _conn = new SqlConnection(Utils.SarConnectionString);
        private SqlCommand _cmd;
        private SqlDataReader _dr;

        public List<UserEntity> RetrieveUserAll()
        {
            var userEntityList = new List<UserEntity>();

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd = new SqlCommand("SELECT * FROM [dbo].[Users]", _conn);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    UserEntity user = UserFactory.CreateUser((RoleType) Int32.Parse(_dr["UserRole"].ToString()));

                    user.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    user.FirstName = _dr["FirstName"].ToString();
                    user.LastName = _dr["LastName"].ToString();
                    user.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    user.Username = _dr["Username"].ToString();
                    user.Picture = _dr["Picture"].ToString();
                    user.Address = _dr["Address"].ToString();
                    user.Phone = _dr["Phone"].ToString();
                    user.Email = _dr["Email"].ToString();
                    user.Status = Int32.Parse(_dr["Status"].ToString());

                    userEntityList.Add(user);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[User] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return userEntityList;
        }

        public UserEntity RetrieveUserById(int userId)
        {
            UserEntity user = null;

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd = new SqlCommand("SELECT * FROM [dbo].[Users] WHERE [UserID] = @userId", _conn);
                _cmd.Parameters.Add("@userId", SqlDbType.Int);
                _cmd.Parameters["@userId"].Value = userId;

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    user = UserFactory.CreateUser((RoleType) Int32.Parse(_dr["UserRole"].ToString()));
                    user.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    user.FirstName = _dr["FirstName"].ToString();
                    user.LastName = _dr["LastName"].ToString();
                    user.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    user.Username = _dr["Username"].ToString();
                    user.Picture = _dr["Picture"].ToString();
                    user.Address = _dr["Address"].ToString();
                    user.Phone = _dr["Phone"].ToString();
                    user.Email = _dr["Email"].ToString();
                    user.Status = Int32.Parse(_dr["Status"].ToString());
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[User] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return user;
        }

        public UserEntity RetrieveUserByUserName(string userName)
        {
            UserEntity user = null;

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd = new SqlCommand("SELECT * FROM [dbo].[Users] WHERE [UserName] = @userName", _conn);
                _cmd.Parameters.Add("@userName", SqlDbType.NVarChar);
                _cmd.Parameters["@userName"].Value = userName;

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                while (_dr.HasRows && _dr.Read())
                {
                    user = UserFactory.CreateUser((RoleType) Int32.Parse(_dr["UserRole"].ToString()));
                    user.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    user.FirstName = _dr["FirstName"].ToString();
                    user.LastName = _dr["LastName"].ToString();
                    user.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    user.Username = _dr["Username"].ToString();
                    user.Picture = _dr["Picture"].ToString();
                    user.Address = _dr["Address"].ToString();
                    user.Phone = _dr["Phone"].ToString();
                    user.Email = _dr["Email"].ToString();
                    user.Status = Int32.Parse(_dr["Status"].ToString());
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[User] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return user;
        }

        public List<UserEntity> RetrieveUserByRoleId(int roleId)
        {
            var userEntityList = new List<UserEntity>();

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd = new SqlCommand("SELECT * FROM [dbo].[Users] WHERE [UserRole] = @userRole", _conn);
                _cmd.Parameters.Add("@userRole", SqlDbType.Int);
                _cmd.Parameters["@userRole"].Value = roleId;

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    UserEntity user = UserFactory.CreateUser((RoleType) Int32.Parse(_dr["UserRole"].ToString()));

                    user.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    user.FirstName = _dr["FirstName"].ToString();
                    user.LastName = _dr["LastName"].ToString();
                    user.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    user.Username = _dr["Username"].ToString();
                    user.Picture = _dr["Picture"].ToString();
                    user.Address = _dr["Address"].ToString();
                    user.Phone = _dr["Phone"].ToString();
                    user.Email = _dr["Email"].ToString();
                    user.Status = Int32.Parse(_dr["Status"].ToString());

                    userEntityList.Add(user);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[User] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return userEntityList;
        }

        public List<StudentEntity> RetrieveStudentsByCourseId(int courseId)
        {
            var studentList = new List<StudentEntity>();

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [Users] A INNER JOIN [StudentCourse] B ON A.UserID=B.StudentID AND B.CourseID = @courseID",
                        _conn);
                _cmd.Parameters.Add("@courseID", SqlDbType.Int);
                _cmd.Parameters["@courseID"].Value = courseId;

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    StudentEntity student = UserFactory.CreateStudent();

                    student.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    student.FirstName = _dr["FirstName"].ToString();
                    student.LastName = _dr["LastName"].ToString();
                    student.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    student.Username = _dr["Username"].ToString();
                    student.Picture = _dr["Picture"].ToString();
                    student.Address = _dr["Address"].ToString();
                    student.Phone = _dr["Phone"].ToString();
                    student.Email = _dr["Email"].ToString();
                    student.Status = Int32.Parse(_dr["Status"].ToString());

                    studentList.Add(student);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[User]-[RetrieveStudentsByCourseId] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return studentList;
        }

        public List<TutorEntity> RetrieveTutorsByGroupId(int groupId)
        {
            var tutorList = new List<TutorEntity>();

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "Select * from GroupManager AS G inner join Users AS U ON G.ManagerID=U.UserID WHERE G.GroupID=@groupID",
                        _conn);
                _cmd.Parameters.Add("@groupID", SqlDbType.Int);
                _cmd.Parameters["@groupID"].Value = groupId;
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    UserEntity user = UserFactory.CreateUser((RoleType) Int32.Parse(_dr["UserRole"].ToString()));
                    user.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    user.FirstName = _dr["FirstName"].ToString();
                    user.LastName = _dr["LastName"].ToString();
                    user.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    user.Username = _dr["Username"].ToString();
                    user.Picture = _dr["Picture"].ToString();
                    user.Address = _dr["Address"].ToString();
                    user.Phone = _dr["Phone"].ToString();
                    user.Email = _dr["Email"].ToString();
                    user.Status = Int32.Parse(_dr["Status"].ToString());

                    tutorList.Add((TutorEntity) user);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Group] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return tutorList;
        }

        public List<StudentEntity> RetrieveStudentsByGroupId(int groupId)
        {
            var studentList = new List<StudentEntity>();

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [Users] A INNER JOIN [StudentGroup] B ON A.UserID=B.StudentID AND B.GroupID = @groupID",
                        _conn);
                _cmd.Parameters.Add("@groupID", SqlDbType.Int);
                _cmd.Parameters["@groupID"].Value = groupId;

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    StudentEntity student = UserFactory.CreateStudent();

                    student.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    student.FirstName = _dr["FirstName"].ToString();
                    student.LastName = _dr["LastName"].ToString();
                    student.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    student.Username = _dr["Username"].ToString();
                    student.Picture = _dr["Picture"].ToString();
                    student.Address = _dr["Address"].ToString();
                    student.Phone = _dr["Phone"].ToString();
                    student.Email = _dr["Email"].ToString();
                    student.Status = Int32.Parse(_dr["Status"].ToString());

                    studentList.Add(student);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[User]-[RetrieveStudentsByGroupId] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return studentList;
        }

        public List<StudentEntity> SearchStudentByName(string text)
        {
            var studentList = new List<StudentEntity>();

            try
            {
                // Consult with your SQL Server administrator for an appropriate connection
                // string to use to connect to your local SQL Server.
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd =
                    new SqlCommand(
                        "SELECT * FROM [dbo].[Users] WHERE (([FirstName] LIKE '%' + @query + '%') OR ([LastName] LIKE '%' + @query + '%')) AND [UserRole] = 1",
                        _conn);
                _cmd.Parameters.AddWithValue("@query", text);

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    StudentEntity student = UserFactory.CreateStudent();

                    student.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    student.FirstName = _dr["FirstName"].ToString();
                    student.LastName = _dr["LastName"].ToString();
                    student.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    student.Username = _dr["Username"].ToString();
                    student.Picture = _dr["Picture"].ToString();
                    student.Address = _dr["Address"].ToString();
                    student.Phone = _dr["Phone"].ToString();
                    student.Email = _dr["Email"].ToString();
                    student.Status = Int32.Parse(_dr["Status"].ToString());

                    studentList.Add(student);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[User]-[Search student] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return studentList;
        }

        public List<TutorEntity> GetAllTutorOutOfGroup(int groupId)
        {
            var tutorList = new List<TutorEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT * FROM [Users] WHERE [UserRole] = 2 " +
                                      "AND [UserID] NOT IN (SELECT [ManagerID] FROM [GroupManager] WHERE [GroupID] = @groupId)",
                    _conn);
                _cmd.Parameters.Add("@groupID", SqlDbType.Int);
                _cmd.Parameters["@groupID"].Value = groupId;
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    UserEntity user = UserFactory.CreateUser((RoleType) Int32.Parse(_dr["UserRole"].ToString()));
                    user.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    user.FirstName = _dr["FirstName"].ToString();
                    user.LastName = _dr["LastName"].ToString();
                    user.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    user.Username = _dr["Username"].ToString();
                    user.Picture = _dr["Picture"].ToString();
                    user.Address = _dr["Address"].ToString();
                    user.Phone = _dr["Phone"].ToString();
                    user.Email = _dr["Email"].ToString();
                    user.Status = Int32.Parse(_dr["Status"].ToString());

                    tutorList.Add((TutorEntity) user);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Group] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return tutorList;
        }

        public List<StudentEntity> GetAllCourseStudentOutOfGroup(int groupId, int courseId)
        {
            var studentList = new List<StudentEntity>();

            try
            {
                _conn.Open();

                // Create SqlCommand to select pwd field from users table given supplied userName.
                _cmd = new SqlCommand("SELECT * FROM [Users] AS U JOIN [StudentCourse] AS SC " +
                                      "ON U.UserID = SC.StudentID LEFT OUTER JOIN [StudentGroup] AS SG " +
                                      "ON SG.StudentID = U.UserID WHERE SC.CourseID = @courseId " +
                                      "AND U.UserID NOT IN (SELECT StudentID FROM [StudentGroup] WHERE GroupID = @groupId)" +
                                      "AND SG.GroupID IS NULL", _conn);
                _cmd.Parameters.Add("@groupID", SqlDbType.Int);
                _cmd.Parameters["@groupID"].Value = groupId;
                _cmd.Parameters.Add("@courseId", SqlDbType.Int);
                _cmd.Parameters["@courseId"].Value = courseId;

                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    StudentEntity student = UserFactory.CreateStudent();

                    student.UserID = Convert.ToInt32(_dr["UserID"].ToString());
                    student.FirstName = _dr["FirstName"].ToString();
                    student.LastName = _dr["LastName"].ToString();
                    student.DOB = DateTime.Parse(_dr["DOB"].ToString());
                    student.Username = _dr["Username"].ToString();
                    student.Picture = _dr["Picture"].ToString();
                    student.Address = _dr["Address"].ToString();
                    student.Phone = _dr["Phone"].ToString();
                    student.Email = _dr["Email"].ToString();
                    student.Status = Int32.Parse(_dr["Status"].ToString());

                    studentList.Add(student);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[User]-[RetrieveStudentsByGroupId] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return studentList;
        }
    }
}